from datetime import datetime, timedelta, date
from pprint import pprint
import random
import pymysql

from src.sql import configs, utils

random.seed(42)


def create_db():
    """
    创建数据库

    :return:
    """
    global db, cursor
    db = pymysql.connect(host=configs.host, user=configs.user, password=configs.password)
    cursor = db.cursor()
    try:
        cursor.execute("create database {}".format(configs.database))
    except pymysql.err.ProgrammingError:
        drop_db()
        cursor.execute("create database {}".format(configs.database))
    db.close()
    db = pymysql.connect(host=configs.host, user=configs.user, password=configs.password, database=configs.database)
    cursor = db.cursor()


def drop_db():
    """
    删除数据库

    :return:
    """
    cursor.execute("drop database {}".format(configs.database))


def create_employee():
    """
    创建employee（员工）表

    :return:
    """
    cursor.execute("create table employee ("
                   "Ename varchar(20) not null, "
                   "Eid char(10) not null check (char_length(Eid) = 10), "
                   "Esex char(1) check(Esex in ('F', 'M')), "
                   "Birthday date, "
                   "primary key (Eid)"
                   ");")


def create_phonenumbers():
    """
    创建phonenumbers（员工的电话）表

    :return:
    """
    cursor.execute("create table phonenumbers ("
                   "Eid char(10) not null check (char_length(Eid) = 10),"
                   "Phonenumber char(11) not null unique check (char_length(Phonenumber) = 11),"
                   "primary key (Eid, Phonenumber),"
                   "foreign key (Eid) references employee(Eid)"
                   ");")


def create_goods():
    """
    创建goods（商品）表

    :return:
    """
    cursor.execute("create table goods ("
                   "Gname varchar(50) not null,"
                   "Gid char(10) not null check (char_length(Gid) = 10),"
                   "Manufacture_date DATE not null,"  # 生产日期
                   "Quality_days int check (Quality_days > 0) not null ,"  # 保质期（天）
                   "Manufacturer varchar(25) not null,"  # 生产商名称
                   "Number int  not null check (Number > 0), "  # 数目
                   "Unit varchar(10) not null check (Unit in ('个', 'g')), "  # 单位
                   "Sprice numeric(10, 2) not null check (Sprice > 0.0),"  # 单位售价
                   "primary key (Gid)"
                   ");")


def create_shelves():
    """
    创建shelves（货架）表

    :return:
    """
    cursor.execute("create table shelves ("
                   "Sname varchar(20) not null,"
                   "Sid char(4) not null check (char_length(Sid) = 4),"
                   "Sdistrict varchar(10) ,"  # 货架所在区域
                   "Snum int ,"  # 货架所在区域中的编号
                   "primary key (Sid)"
                   ");")


def create_warehouse():
    """
    创建warehouse（仓库）表

    :return:
    """
    cursor.execute("create table warehouse ("
                   "Wname varchar(20) not null,"
                   "Wid char(4) not null check (char_length(Wid) = 4),"
                   "Wdistrict varchar(10) ,"  # 仓库所在区
                   "Wstreet varchar(10),"  # 仓库所在街道
                   "Wnum int, "  # 仓库所在门牌号
                   "primary key (Wid)"
                   ");")


def create_supplier():
    """
    创建supplier（供货商）表

    :return:
    """
    cursor.execute("create table supplier ("
                   "Su_name varchar(20) not null,"
                   "Gid char(10) not null check (char_length(Gid) = 10),"  # 商品号
                   "Su_sex char check (Su_sex in ('F', 'M')),"
                   "Su_phonenumber varchar(11) unique check (char_length(Su_phonenumber) = 11), "  # 电话号码
                   "primary key (Su_name, Gid), "
                   "foreign key (Gid) references goods(Gid) "
                   ");")


def create_purchases():
    """
    创建仓库和商品之间的联系-进货（Purchases）

    :return:
    """
    cursor.execute("create table purchases ("
                   "Wid char(4) not null check (char_length(Wid) = 4),"
                   "Gid char(10) not null check (char_length(Gid) = 10),"
                   "Purchase_date Date,"  # 进货日期
                   "Purchase_price numeric(10, 2) not null ,"  # 单位进价
                   "Pnumber int, "  # 进货数量
                   "primary key (Wid, Gid), "
                   "foreign key (Wid) references warehouse(Wid), "
                   "foreign key (Gid) references goods(Gid) "
                   ");")


def create_sales():
    """
    创建货架和商品之间的联系-销售（Sales）

    :return:
    """
    cursor.execute("create table sales ("
                   "Said int UNSIGNED NOT NULL AUTO_INCREMENT, "
                   "Sid char(4) not null check (char_length(Sid) = 4),"  # 货架号
                   "Gid char(10) not null check (char_length(Gid) = 10),"  # 商品号
                   "Eid char(10) not null check (char_length(Eid) = 10),"  # 收银员号
                   "Stime DATETIME not null ,"  # 销售时间
                   "Srecord_num int not null ,"  # 销售数目
                   "Srecord_price numeric(10, 2) not null , "  # 销售单价
                   "Pay_account varchar(20),"  # 支付账号，如果为现金支付，则为-1
                   "Pay_mode varchar(10) not null check (Pay_mode in ('Wechat', 'Alipay', 'Credit', 'Cash')), "  # 支付方式
                   "primary key (Said), "
                   "foreign key (Sid) references shelves(Sid), "
                   "foreign key (Gid) references goods(Gid),"
                   "foreign key (Eid) references employee(Eid)"
                   ");")


def create_launches_off():
    """
    创建货架和商品和仓库之间的联系-上下架（Launches_off）

    :return:
    """
    cursor.execute("create table launches_off ("
                   "Lid int UNSIGNED NOT NULL AUTO_INCREMENT, "
                   "Sid char(4) not null check (char_length(Sid) = 4),"  # 货架号
                   "Gid char(10) not null check (char_length(Gid) = 10),"  # 商品号
                   "Wid char(4) not null check (char_length(Wid) = 4),"  # 仓库号
                   "Launch_date DATE ,"  # 上架日期
                   "Off_shelf_date DATE ,"  # 下架日期
                   "Change_num int not null , "  # 上下架数目
                   "primary key (Lid), "
                   "foreign key (Sid) references shelves(Sid), "
                   "foreign key (Gid) references goods(Gid),"
                   "foreign key (Wid) references warehouse(Wid)"
                   ");")


def create_manages():
    """
    创建员工和仓库之间的联系-管理仓库（Manages）

    :return:
    """
    cursor.execute("create table manages ("
                   "Mid int UNSIGNED NOT NULL AUTO_INCREMENT,"  # 管理仓库记录号
                   "Eid char(10) not null check (char_length(Eid) = 10),"  # 仓库管理员号
                   "Wid char(4) not null check (char_length(Wid) = 4),"  # 仓库号
                   "Appointment_date DATE ,"  # 上任日期
                   "Departure_date DATE ,"  # 离任日期
                   "primary key (Mid), "
                   "foreign key (Eid) references employee(Eid), "
                   "foreign key (Wid) references warehouse(Wid)"
                   ");")


def create_tables():
    """
    创建所用表

    :return: 无
    """
    create_db()
    # 创建实体
    create_employee()
    create_phonenumbers()
    create_goods()
    create_shelves()
    create_warehouse()
    create_supplier()
    # 创建关系
    create_purchases()
    create_sales()
    create_launches_off()
    create_manages()
    return db, cursor


def insert_warehouse(nums: int):
    """
    向warehouse中插入数据。

    :param nums: 数据条数
    :return:
    """
    for i in range(nums):
        Wname = 'Wname{}'.format(i)
        Wid = '{:0>4}'.format(i)
        Wdistrict = configs.Wdistricts[i % len(configs.Wdistricts)]
        Wstreet = configs.Wstreets[Wdistrict][i % len(configs.Wstreets[Wdistrict])]
        Wnum = i % configs.Max_house_numbers
        utils.warehouse_add(cursor, Wname, Wid, Wdistrict, Wstreet, Wnum)


def insert_shelves(nums: int):
    """
    向shelves中插入数据。

    :param nums: 数据条数
    :return:
    """
    for i in range(nums):
        Sname = 'Sname{}'.format(i)
        Sid = '{:0>4}'.format(i)
        Sdistrict = configs.Sdistricts[i % len(configs.Sdistricts)]
        Snum = i % configs.Max_house_numbers
        utils.shelves_add(cursor, Sname, Sid, Sdistrict, Snum)


def insert_employee(nums: int):
    """
    向employee中插入数据。

    :param nums: 数据条数
    :return:
    """
    for i in range(nums):
        Ename = 'Ename{}'.format(i)
        Eid = '{:0>10}'.format(i)
        Esex = configs.sexes[i % len(configs.sexes)]
        Birthday = configs.Birthdays[random.randint(0, len(configs.Birthdays) - 1)]
        Phonenumbers = []
        max_count = random.randint(1, configs.max_phonenumbers_num)
        count = 0
        while count < max_count:
            Phonenumber = configs.Phonenumbers[random.randint(0, configs.max_phonenumbers_num_base - 1)]
            if Phonenumber in configs.selected_phonenumbers:
                continue
            Phonenumbers.append(Phonenumber)
            configs.selected_phonenumbers.add(Phonenumber)
            count += 1
        utils.employee_add(cursor, Ename, Eid, Esex, Birthday, Phonenumbers)


def insert_goods(nums: int):
    """
    向goods中插入数据。采用进货的方式来进行

    :param nums: 数据条数
    :return:
    """
    for i in range(nums):
        Gname = 'Gname{}'.format(i)
        Gid = '{:0>10}'.format(i)
        Manufacture_date = configs.Manufacture_dates[random.randint(0, configs.Manufacture_max_days - 1)]
        Quality_days = random.randint(5, configs.max_Quality_days)
        Manufacturer = 'Manufacturer{}'.format(i % configs.Manufacturers_num)
        Unit = configs.Units[random.randint(0, len(configs.Units) - 1)]
        Sprice = random.random() * 1000
        Wid = '{:0>4}'.format(random.randint(0, configs.warehouse_nums - 1))
        Su_name = 'Su_name{}'.format(i % configs.Manufacturers_num)
        Su_sex = configs.sexes[random.randint(0, 1)]
        Su_phonenumber = None
        while Su_phonenumber is None:
            temp = configs.Phonenumbers[random.randint(0, configs.max_phonenumbers_num_base)]
            if temp not in configs.selected_phonenumbers:
                Su_phonenumber = temp
                configs.selected_phonenumbers.add(temp)
        Purchase_date = (datetime.fromisoformat(Manufacture_date) + timedelta(
            days=random.randint(0, min(datetime.utcfromtimestamp(
                (datetime.fromisoformat(configs.now_date).timestamp() - datetime.fromisoformat(
                    Manufacture_date).timestamp())).day, Quality_days)) - 1)).strftime(
            "%Y-%m-%d")

        Purchase_price = Sprice * random.random()
        Pnumber = random.randint(1, configs.max_Pnumber)
        utils.purchases_add(cursor, Gname, Gid, Manufacture_date, Quality_days,
                            Manufacturer, Unit, Sprice, Wid, Su_name, Su_sex, Su_phonenumber,
                            Purchase_date, Purchase_price, Pnumber)


def insert_launches_off(launch_nums: int, off_nums: int):
    """
    向launches_off中插入数据。包括上下架操作。

    :param launch_nums: 上架数据条数
    :param off_nums: 下架数据条数
    :return:
    """
    if launch_nums < off_nums:
        raise Exception('launch_nums应该大于off_nums！')
    i = 0
    launch_dict = []
    while i < launch_nums:
        Gid = '{:0>10}'.format(random.randint(0, configs.goods_nums - 1))
        goods_left_warehouse_all = utils.get_goods_left_warehouse_all(cursor, Gid)
        for Wid, Pnumber in goods_left_warehouse_all:
            Sid = '{:0>4}'.format(random.randint(0, configs.shelves_nums - 1))
            Purchase_date = utils.select_star_get(cursor, 'Purchases', Gid)['Purchase_date']
            goods_info = utils.select_star_get(cursor, 'goods', Gid)
            Manufacture_date, Quality_days = goods_info['Manufacture_date'], goods_info['Quality_days']
            Launch_date = (Purchase_date + timedelta(
                days=random.randint(0, min(datetime.utcfromtimestamp(
                    (datetime.fromisoformat(configs.now_date).timestamp() -
                     utils.date2datetime(Purchase_date).timestamp())).day, Quality_days)) - 1)).strftime(
                "%Y-%m-%d")
            Change_num = random.randint(1, Pnumber)
            utils.launches_off_add(cursor, Wid, Gid, Sid, Launch_date, None, Change_num)
            launch_dict.append([Wid, Gid, Sid, Launch_date, None, Change_num, Quality_days])
            i += 1
            if i >= launch_nums:
                break
    i = 0
    while i < off_nums:
        for Wid, Gid, Sid, Launch_date, _, Change_num, Quality_days in launch_dict:
            Off_shelf_date = (datetime.fromisoformat(Launch_date) + timedelta(
                days=random.randint(0, min(datetime.utcfromtimestamp(
                    (datetime.fromisoformat(configs.now_date).timestamp() -
                     datetime.fromisoformat(Launch_date).timestamp())).day, Quality_days)))).strftime(
                "%Y-%m-%d")
            Change_num = random.randint(1, Change_num)
            utils.launches_off_add(cursor, Wid, Gid, Sid, None, Off_shelf_date, Change_num)
            i += 1
            if i >= off_nums:
                break


def insert_sales(sales_nums: int):
    """
    向sales中插入数据。售出商品。

    :param sales_nums: 售出商品记录条数
    :return:
    """
    i = 0
    while i < sales_nums:
        Gid = '{:0>10}'.format(random.randint(0, configs.goods_nums - 1))
        Eid = '{:0>10}'.format(random.randint(0, configs.employee_nums - 1))
        result = utils.get_goods_left_shelves(cursor, Gid)
        if result is not None:
            Wid, letf_number, latest_launch_date = result
            if letf_number <= 1:
                continue
            Srecord_num = random.randint(1, letf_number)
            Srecord_price = float(utils.select_star_get(cursor, 'goods', Gid)['Sprice']) * (
                    1 + random.random() * configs.max_profit_margin)
            if i % 10 == 0:
                Pay_account = -1
                Pay_mode = 'Cash'
            else:
                Pay_account = '{:0>10}'.format(random.randint(0, configs.pay_account_nums))
                Pay_mode = configs.Pay_modes[:-1][random.randint(0, len(configs.Pay_modes) - 2)]
            goods_info = utils.select_star_get(cursor, 'goods', Gid)
            Manufacture_date, Quality_days = goods_info['Manufacture_date'], goods_info['Quality_days']
            try:
                Stime = (utils.date2datetime(latest_launch_date) + timedelta(
                    days=random.randint(0, min(datetime.utcfromtimestamp(
                        (datetime.fromisoformat(configs.now_date).timestamp() -
                         utils.date2datetime(latest_launch_date).timestamp())).day, Quality_days) - 2),
                    minutes=random.randint(0, 1440))).strftime(
                    "%Y-%m-%d %H:%M:%S")
            except ValueError:
                continue
            utils.sales_add(cursor, Gid, Eid, Stime, Srecord_num, Srecord_price, Pay_account, Pay_mode)
            i += 1


def insert_manages(manages_nums: int):
    """
    向manages中插入数据。管理仓库。

    :param manages_nums: 管理员上下任记录条数
    :return:
    """
    i = 0
    while i < manages_nums:
        Eid = '{:0>10}'.format(random.randint(0, configs.employee_nums - 1))
        Wid = '{:0>4}'.format(random.randint(0, configs.warehouse_nums - 1))
        # 上任
        Appointment_date = configs.Appointment_dates[random.randint(0, configs.Appointment_max_days - 1)]
        utils.manages_add(cursor, Eid, Wid, Appointment_date, None)
        i += 1
        if i >= manages_nums:
            break
        # 下任
        Appointment_date = date.fromisoformat(Appointment_date)
        Departure_date = (Appointment_date + timedelta(
            days=random.randint(0, datetime.utcfromtimestamp(
                (datetime.fromisoformat(configs.now_date).timestamp() - utils.date2datetime(
                    Appointment_date).timestamp())).day))).strftime(
            "%Y-%m-%d")
        utils.manages_add(cursor, Eid, Wid, None, Departure_date)
        i += 1


def insert_data():
    """
    插入数据

    :return: 无
    """
    insert_warehouse(configs.warehouse_nums)
    insert_shelves(configs.shelves_nums)
    insert_employee(configs.employee_nums)
    insert_goods(configs.goods_nums)
    insert_launches_off(configs.launch_nums, configs.off_nums)  # 上下架
    insert_sales(configs.sales_nums)
    insert_manages(configs.manages_nums)
    return db, cursor


def create_purchase_history_view():
    """
    创建历史进货记录

    :return:
    """
    cursor.execute("create view purchase_history_view as select "
                   "Wid, "
                   "Gid, "
                   "purchase_date, "
                   "purchase_price, "
                   "pnumber, "
                   "Su_name, "
                   "Su_sex, "
                   "Su_phonenumber "
                   "from (purchases natural join supplier); "
                   )


def create_employee_manage_view():
    """
    创建员工信息-管理仓库视图

    :return:
    """
    cursor.execute("create view employee_manage_view as (select "
                   "Ename, "
                   "employee.Eid, "
                   "Esex, "
                   "Birthday, "
                   "ROUND(DATEDIFF(CURDATE(), Birthday)/365.2422) as Eage, "
                   "Wid, "
                   "Appointment_date "
                   "from (employee left outer join manages on employee.Eid=manages.Eid and Appointment_date is not null and Departure_date is null)); "
                   )


def create_view():
    """
    创建视图

    :return: 无
    """
    create_purchase_history_view()
    create_employee_manage_view()
    return db, cursor


def create_employee_index():
    """
    为创建employee的Ename创建索引

    :return:
    """
    cursor.execute("create index Ename_idx on employee(Ename) using hash;")


def create_goods_index():
    """
    为创建goods的Gname,创建索引

    :return:
    """
    cursor.execute("create index Gname_idx on goods(Gname) using hash;")


def create_launches_off_index():
    """
    为创建launches_off的Launch_date,创建索引

    :return:
    """
    cursor.execute("create index Launch_date_idx on launches_off(Launch_date) using hash;")


def create_manages_index():
    """
    为创建manages的Appointment_date,创建索引

    :return:
    """
    cursor.execute("create index manages_idx on manages(Appointment_date) using hash;")


def create_purchases_index():
    """
    为创建purchases的Purchase_date,创建索引

    :return:
    """
    cursor.execute("create index purchases_idx on purchases(Purchase_date) using hash;")


def create_sales_index():
    """
    为创建sales的Stime,创建索引

    :return:
    """
    cursor.execute("create index sales_idx on sales(Stime) using hash;")


def create_shelves_index():
    """
    为创建shelves的Sname,创建索引

    :return:
    """
    cursor.execute("create index shelves_idx on shelves(Sname) using hash;")


def create_warehouse_index():
    """
    为创建warehouse的Wname,创建索引

    :return:
    """
    cursor.execute("create index warehouse_idx on warehouse(Wname) using hash;")


def create_index():
    """
    创建索引

    :return: 无
    """
    create_employee_index()
    create_goods_index()
    create_launches_off_index()
    create_manages_index()
    create_purchases_index()
    create_sales_index()
    create_shelves_index()
    create_warehouse_index()
    return db, cursor


def check():
    """
    打印信息，检查

    :return:
    """
    cursor.execute("show tables;")
    for (table,) in cursor.fetchall():
        print('-' * 50)
        print(table)
        cursor.execute("describe {};".format(table))
        pprint(cursor.fetchall())
        cursor.execute("select * from {} limit 10;".format(table))
        pprint(cursor.fetchall())
        cursor.execute("show keys from {};".format(table))
        print('索引')
        print(cursor.fetchall())
        print('-' * 50)


if __name__ == "__main__":
    global db, cursor
    create_tables()
    insert_data()
    create_view()
    create_index()
    check()
    db.close()
